/*
 * Student model
 */
package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.StudentBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class Student {

	private static Logger log = Logger.getLogger(Student.class.getName());
	private static Database db = Database.getInstance();

	public static void create(StudentBean sb, String password) {
		try {
			Connection conn = db.getConnection();
			String query = "insert into `students`" +
					"(`college_id`, `first_name`, `last_name`, " +
					"`cnp`, `email`, `password`, `active`) values " +
					"( ?, ?, ?, ?, ?, md5(?), ?)";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, sb.getCollegeId());
			st.setString(2, sb.getFirstName());
			st.setString(3, sb.getLastName());
			st.setString(4, sb.getCnp());
			st.setString(5, sb.getEmail());
			st.setString(6, password);
			st.setBoolean(7, sb.isActive());

			st.execute();

			st.close();
			conn.close();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void update(StudentBean sb) {
		try {
			Connection conn = db.getConnection();
			String query = "update `students` set `college_id`=?, " +
					"`first_name`=?, `last_name`=?, `cnp`=?, " +
					"`email`=?, `active`=? where `student_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, sb.getCollegeId());
			st.setString(2, sb.getFirstName());
			st.setString(3, sb.getLastName());
			st.setString(4, sb.getCnp());
			st.setString(5, sb.getEmail());
			st.setBoolean(6, sb.isActive());
			st.setInt(7, sb.getStudentId());

			st.executeUpdate();

			st.close();
			conn.close();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void updatePassword(int studentId, String password) {
		try {
			Connection conn = db.getConnection();
			String query = "update `students` set `password`=md5(?) " +
					"where `student_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, password);
			st.setInt(2, studentId);

			st.executeUpdate();

			st.close();
			conn.close();

		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static StudentBean fetch(String cnp, String password) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `students` " +
					"where `cnp`=? and `password`=md5(?)";

			PreparedStatement st = conn.prepareStatement(query);
			st.setString(1, cnp);
			st.setString(2, password);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				StudentBean sb = new StudentBean();
				sb.fill(rs);

				rs.close();
				st.close();
				conn.close();

				return sb;
			}

			st.close();
			conn.close();

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static StudentBean fetch(int studentId) {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `students` where `student_id`=?";

			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, studentId);

			ResultSet rs = st.executeQuery();
			if (rs.next()) {
				StudentBean sb = new StudentBean();
				sb.fill(rs);

				rs.close();
				st.close();
				conn.close();

				return sb;
			}

			st.close();
			conn.close();

			return null;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static List<StudentBean> fetchAll() {
		try {
			Connection conn = db.getConnection();
			String query = "select * from `students`";
			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();
			List<StudentBean> ret = new LinkedList<StudentBean>();
			while (rs.next()) {
				StudentBean sb = new StudentBean();
				sb.fill(rs);
				ret.add(sb);
			}
			
			return ret;
		} catch (SQLException ex) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}

	public static void remove( int id ) {
		try {
			Connection conn = db.getConnection();
			String query = "delete from `students` where `student_id`=?";
			PreparedStatement st = conn.prepareStatement(query);
			st.setInt(1, id);
			st.execute();
		} catch( SQLException ex ) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}
